/* QAD_Where-UsedReport.p - WHERE-USED REPORT                                 */
/* Copyright 1986-2011 QAD Inc., Santa Barbara, CA, USA.                      */
/* All rights reserved worldwide.  This is an unpublished work.               */
/* $Id:: QAD_TransactionsAccountingReport.p 1046 2010-09-10 09:08:19Z jvz  $: */
/*                                                                            */
/* Report matches legacy fmwurp.p for use by QAD Reporting Framework.         */
/*                                                                            */

{mfsubdirs.i}
{{&US_BBI}mfdeclre.i}
{{&US_BBI}gplabel.i}

{com/qad/shell/report/dsReportRequest.i}
{com/qad/shell/report/ReportConstants.i}

/*============================================================================*/
/* ****************************  TEMP TABLES  ******************************* */
/*============================================================================*/

/* TEMP-TABLE DEFINITION BLOCK */

define temp-table ttParent no-undo
   field ps_comp       like ps_mstr.ps_comp
   field bombatch      like bom_mstr.bom_batch
   field um            like pt_mstr.pt_um
   field phantom       as   character
   field iss_pol       as   logical
   field ptal_alt_part like ptal_mstr.ptal_alt_part
   field description   like pt_mstr.pt_desc1
   field unique_id     as   integer
.

define temp-table ttComponent no-undo like ps_mstr
   field lvl           as   character
   field bombatch      like bom_mstr.bom_batch
   field um            like pt_mstr.pt_um
   field phantom       as   character
   field iss_pol       as   logical
   field ptal_alt_part like ptal_mstr.ptal_alt_part
   field description   like pt_mstr.pt_desc1
   field unique_id     as   integer
.

define temp-table ttReportOptions no-undo
   field eff_date as date
   field maxlevel as integer
   field skpge    as logical
.

define dataset dsReportResults for ttParent, ttComponent, ttReportOptions.

/*============================================================================*/
/* *****************************  MAIN BLOCK  ******************************* */
/*============================================================================*/
define input parameter runReport    as logical no-undo.
define input parameter reportHandle as handle  no-undo.
define input parameter dataset      for dsReportRequest.
define output parameter dataset-handle phReportResults.

{com/qad/shell/report/reporting.i}

define variable vhDS as handle    no-undo.

empty temp-table ttParent        no-error.
empty temp-table ttComponent     no-error.
empty temp-table ttReportOptions no-error.

for first ttReportRequest no-lock:

   vhDS = dataset dsReportResults:handle.
   run FillMetaData in reportHandle(
      vhDS, "QAD_Where-UsedReport.meta.xml").

   run SetReportMetaData (input reportHandle).

   if runReport then do:
      run RunReport
         (output dataset-handle phReportResults).
   end.

end.

PROCEDURE SetReportMetaData:

   define input parameter  ipReportHandle as handle.

   run SetFieldMetaParameter in ipReportHandle ("ttReportOptions",
         "skpge", "DataFormat", getLabel("YES/NO")).

END PROCEDURE. /* PROCEDURE ModifyMetaData */

/* DATA RETRIEVING LOGIC BLOCK */
PROCEDURE RunReport:

   define output parameter dataset-handle phReportResults.

   /* RETRIEVE THE DATA FROM DATABASE */
   define variable queryString   as character no-undo.
   define variable hMainQuery    as handle    no-undo.
   define query    MainQuery     for ps_mstr, ptal_mstr.
   define variable eff_date as date no-undo.
   define variable level as integer no-undo.
   define variable maxlevel as integer no-undo.
   define variable comp like ps_mstr.ps_comp no-undo.
   define variable eff_date as date no-undo.
   define variable component like ps_comp no-undo.
   define variable desc1   like pt_mstr.pt_desc1 no-undo.
   define variable um      like pt_mstr.pt_um no-undo.
   define variable phantom like mfc_ctrl.mfc_logical no-undo. 
   define variable iss_pol as logical no-undo.
   define variable record  as integer extent 1000 no-undo.
   define variable lvl     as character format "x(11)" no-undo. 
   define variable bombatch   like bom_mstr.bom_batch no-undo.
   define variable line2      like ps_mstr.ps_par no-undo. 
   define variable l_leave    like mfc_ctrl.mfc_logical no-undo.

   define buffer ptmstr for pt_mstr.

   define temp-table item-bom
      field  item like pt_part
      field  bom  like pt_bom_code
      field  par  like ps_par
      field  ref  like ps_ref
      index  item-bom-par is unique item bom par ref.
   
   assign
      hMainQuery           = query MainQuery:handle
      eff_date             = Date(GetFilterValue("ttReportOptions", 
                              "eff_date", today))
      maxlevel             = Integer(GetFilterValue("ttReportOptions",
                              "maxlevel",""))
      .

   queryString = "for each ps_mstr no-lock where ps_domain = "
                  + quoter(global_domain).

   run AddAllConditions in reportHandle
      ("ps_mstr", input-output queryString).

   queryString = queryString + " and (" 
                  + " (eff_date >= ps_start and ps_end = ? )"
                  + " or (ps_start = ? and eff_date <= ps_end)"
                  + " or (ps_start = ? and ps_end    = ?)"
                  + " or (eff_date >= ps_start and eff_date <= ps_end))".

   queryString = queryString + ", first ptal_mstr no-lock ".

   if not IsConditionEmpty("ttParent", "alt_part")
   then do:
      queryString = queryString + " where ptal_mstr.ptal_domain = "
                     + quoter(global_domain)
                     + " and ptal_mstr.ptal_part = ps_mstr.ps_comp "
                     + " and ptal_mstr.ptal_active ".

      run AddAllConditions in reportHandle
         ( "ptal_mstr" , input-output queryString ).
   end. /* if not IsConditionEmpty(...) */

   queryString = queryString + ":".

   hMainQuery:query-prepare(queryString).
   hMainQuery:query-open().
   hMainQuery:get-next().

   repeat while not hMainQuery:query-off-end:

      assign
         comp        = ps_comp
         level       = 1
         component   = ps_comp
         desc1       = getTermLabel("ITEM_NOT_IN_INVENTORY",24)
         um          = ""
         iss_pol     = no
         phantom     = no.

      for first pt_mstr no-lock
         where pt_mstr.pt_domain = global_domain
         and   pt_part = ps_comp:
      end.

      if available pt_mstr 
      then do:
         assign
            um       = pt_um
            desc1    = pt_desc1
            phantom  = pt_phantom.
         
         if pt_iss_pol = {&ISSUE-POLICY-NONE} 
         then do:
            iss_pol = no.
         end.
         else do: 
            iss_pol = yes.
         end. /* if pt_iss_pol = ... */
      end. /* if available pt_mstr */

      for first bom_mstr no-lock
         where bom_mstr.bom_domain = global_domain
         and   bom_parent = ps_comp:
      end.

      if available bom_mstr then do:

         if desc1 = getTermLabel("ITEM_NOT_IN_INVENTORY",24)
         then desc1 = bom_desc.

         if bom_batch <> 0 
         then do:
            bombatch = bom_batch.
         end.
         else do:
            bombatch = 1.
         end. /* if bom_batch <> 0 */

         um = bom_batch_um.
      end. /* if available bom_mstr */

      /* START DISPLAY */
      if available pt_mstr then line2 = pt_desc2.

      create ttParent.
      assign
         unique_id_parent     = unique_id_parent + 1
         ttParent.ps_comp     = component
         ttParent.bombatch    = bombatch
         ttParent.um          = um
         ttParent.iss_pol     = iss_pol
         ttParent.unique_id   = unique_id_parent.
      
      if phantom
      then do:
         ttParent.phantom = getTermLabel("YES").
      end. /* if phantom */
      
      if available bom_mstr and not(can-find 
         (first pt_mstr where pt_mstr.pt_domain = global_domain
            and pt_part = bom_parent))
      then do:
         ttParent.phantom = "BOM".
      end. /* if available bom_mstr */

      if desc1 <> ""
      then do:
         ttParent.description = desc1 + "~n".
      end.

      if line2 <> ""
      then do:
         ttParent.description = ttParent.description + line2.
      end. /* if line2 <> "" */

      repeat:

         if not available ps_mstr
         then do:
            
            repeat:

               level = level - 1.
               if level < 1 then leave.

               for first ps_mstr
                  where recid(ps_mstr) = record[level]
               no-lock:
               end.

               if available ps_mstr
               then do:
                  comp = ps_par.
               end.

               run p_find_ptp_det(input false, input true).

               if available ps_mstr 
               then do:

                  run p_find_ptp_det(input true, input false).

                  level = level + 1.
                  leave.

               end. /* IF AVAIL ps_mstr */


               for first ps_mstr
                  where recid(ps_mstr) = record[level]
               no-lock:
               end.

               if available ps_mstr
               then do:
                  comp = ps_comp.
               end.

               find next ps_mstr use-index ps_comp
                  where ps_domain  = global_domain
                  and   ps_comp    = comp
                  and  ((ps_start <= eff_date and
                         ps_end    = ?)
                     or (ps_start  = ?        and
                         ps_end   >= eff_date)
                     or (ps_start  = ?        and
                         ps_end    = ?)
                     or (ps_start <= eff_date and
                         ps_end   >= eff_date))
               no-lock no-error.

               if available ps_mstr
               then do:

                  run p_find_ptp_det(input false, input false).

                  leave.
               end.  /* IF AVAILABLE ps_mstr */
            end.  /* REPEAT */
         end. /* IF NOT AVAILABLE ps_mstr */

         if level < 1 then leave.

         if available ps_mstr
            and (eff_date = ? or  (eff_date <> ?
            and (ps_start = ? or ps_start <= eff_date)
            and (ps_end = ? or eff_date <= ps_end)))
         then do:
            
            assign
               desc1    = getTermLabel("ITEM_NOT_IN_INVENTORY",24)
               um       = ""
               iss_pol  = no
               phantom  = no.

            for first ptmstr
               where ptmstr.pt_domain = global_domain
               and   ptmstr.pt_part = ps_par
            no-lock:
            end.

            if available ptmstr 
            then do:
               assign
                  um       = ptmstr.pt_um
                  desc1    = ptmstr.pt_desc1
                  phantom  = ptmstr.pt_phantom.
               
               if ptmstr.pt_iss_pol = {&ISSUE-POLICY-NONE} 
               then do:
                  iss_pol = no.
               end.
               else do: 
                  iss_pol = yes.
               end. /* if ptmstr.pt_iss_pol = ... */
            end. /* if available ptmstr */

            for first bom_mstr no-lock
               where bom_mstr.bom_domain = global_domain
               and  bom_parent = ps_par:
            end.

            if available bom_mstr then do:
               
               if desc1 = getTermLabel("ITEM_NOT_IN_INVENTORY",24)
                  then desc1 = bom_desc.
               
               if bom_batch <> 0 
               then do:
                  bombatch = bom_batch.
               end.
               else do:
                  bombatch = 1.
               end. /* if bom_batch <> 0 */
               
               um = bom_batch_um.
            end. /* if available bom_mstr */

            record[level] = recid(ps_mstr).

            lvl = "........".
            lvl = substring(lvl,1,min (level - 1,10)) + string(level).
            if length(lvl) > 11
            then do:
               lvl = substring(lvl,length (lvl) - 10,11).
            end. /* if length(lvl) ...*/

            if available ptmstr then line2 = ptmstr.pt_desc2.

            create ttComponent.
            buffer-copy ps_mstr to ttComponent no-error.
            
            assign
               unique_id_comp          = unique_id_comp + 1
               ttComponent.lvl         = lvl
               ttComponent.bombatch    = bombatch
               ttComponent.um          = um
               ttComponent.iss_pol     = iss_pol
               ttComponent.unique_id   = unique_id_comp
               ttComponent.ref_id      = unique_id_parent.
            
            if phantom
            then do:
               ttComponent.phantom  = getTermLabel("YES").
            end.
            
            if available bom_mstr and not(can-find 
               (first pt_mstr where pt_mstr.pt_domain = global_domain
               and pt_part = bom_parent))
            then do:
               ttComponent.phantom = "BOM".
            end. /* if available bom_mstr */

            if desc1 <> ""
            then do:
               ttComponent.description = desc1 + "~n".
            end.

            if line2 <> ""
            then do:
               ttComponent.description = ttComponent.description + line2.
            end. /* if line2 <> "" */

            if level < maxlevel or maxlevel = 0
            then do:

               assign
                  comp = ps_par
                  level = level + 1.

               for first ps_mstr
                  use-index ps_comp
                  where ps_domain  = global_domain
                  and   ps_comp    = comp
                  and  ((ps_start <= eff_date and
                         ps_end    = ?)
                     or (ps_start  = ?        and
                         ps_end   >= eff_date)
                     or (ps_start  = ?        and
                         ps_end    = ?)
                     or (ps_start <= eff_date and
                         ps_end   >= eff_date))
               no-lock:
               end. /* FOR FIRST ps_mstr */

               if available ps_mstr
               then do:

                  run p_find_ptp_det(input false, input false).

               end. /* IF AVAIL ps_mstr */

            end. /* IF level < maxlevel OR maxlevel = 0 */
            else do:

               find next ps_mstr use-index ps_comp
                  where ps_domain  = global_domain
                  and   ps_comp    = comp
                  and  ((ps_start <= eff_date and
                         ps_end    = ?)
                     or (ps_start  = ?        and
                         ps_end   >= eff_date)
                     or (ps_start  = ?        and
                         ps_end    = ?)
                     or (ps_start <= eff_date and
                         ps_end   >= eff_date))
               no-lock no-error.

               if not available ps_mstr
               then do:
                  if level <> 1
                     then
                  run p_find_ptp_det(input false,
                     input true).

               end.  /* IF NOT AVAILABLE ps_mstr */
               else do:
                  run p_find_ptp_det(input false, input false).
               end.
            end. /* ELSE DO */
         end.  /* IF eff_date = ? OR (eff_date <> ?... */
         else do:
            find next ps_mstr use-index ps_comp
               where ps_domain  = global_domain
               and   ps_comp    = comp
               and  ((ps_start <= eff_date and
                      ps_end    = ?)
                  or (ps_start  = ?        and
                      ps_end   >= eff_date)
                  or (ps_start  = ?        and
                      ps_end    = ?)
                  or (ps_start <= eff_date and
                      ps_end   >= eff_date))
            no-lock no-error.

         end. /* ELSE DO */
      end.  /* REPEAT */
      
      hMainQuery:get-next().
   end. /* REPEAT QUERY */

   hMainQuery:query-close().
   delete object hMainQuery.
   phReportResults = dataset dsReportResults:handle.

END PROCEDURE.


/* GO THRU ITEM PLANNING DETAIL TABLE TO FIND THE PART */
/* AND THEN CHECK FOR ITS BOM                          */
procedure p_find_ptp_det:

   define input  parameter ip-check-level   like mfc_logical no-undo.
   define input  parameter ip-find-psmstr   like mfc_logical no-undo.

   define variable l_ptp_avail like mfc_logical no-undo.

   l_ptp_avail = no.

   if ip-find-psmstr
      then
   for first ps_mstr
      where  recid(ps_mstr) =  -1
   no-lock:
   end.

   for each ptp_det
         fields (ptp_domain ptp_bom_code ptp_part)
         where ptp_domain   = global_domain
         and   ptp_bom_code = comp
      no-lock:

      l_ptp_avail = yes.

      if ip-find-psmstr
      then do:

         for first ps_mstr
            use-index ps_comp
            where ps_domain   = global_domain
            and   ps_comp     = ptp_det.ptp_part
            and  ((ps_start  <= eff_date and
                   ps_end     = ?)
               or (ps_start   = ?        and
                   ps_end    >= eff_date)
               or (ps_start   = ?        and
                   ps_end     = ?)
               or (ps_start  <= eff_date and
                   ps_end    >= eff_date))
         no-lock:
         end.

         if not available ps_mstr
         then do:
            next.
         end.

      end. /* IF ip-find-psmstr */

      run p_check_item_bom(input  ptp_det.ptp_part,
         input  ptp_det.ptp_bom_code,
         input  ps_mstr.ps_par,
         input  ps_mstr.ps_ref,
         input  ip-check-level,
         output l_leave).

      if l_leave
      then do:

         l_leave = false.
         leave.

      end. /* IF l_leave */
   end. /* FOR EACH ptp_det */

   if not l_ptp_avail
   then do:
      for each pt_mstr
            fields(pt_domain pt_part pt_bom_code)
            where pt_domain   = global_domain
            and   pt_bom_code = comp
         no-lock:

         if ip-find-psmstr
         then do:
            for first ps_mstr
               use-index ps_comp
               where ps_domain   = global_domain
               and   ps_comp     = pt_part
               and  ((ps_start  <= eff_date and
                      ps_end     = ?)
                  or (ps_start   = ?        and
                      ps_end    >= eff_date)
                  or (ps_start   = ?        and
                      ps_end     = ?)
                  or (ps_start  <= eff_date and
                      ps_end    >= eff_date))
            no-lock:
            end. /* FOR FIRST ps_mstr */

            if not available ps_mstr
               then
               next.

         end. /* IF ip-find-psmstr */

         run p_check_item_bom(input  pt_part,
            input  pt_bom_code,
            input  ps_mstr.ps_par,
            input  ps_mstr.ps_ref,
            input  ip-check-level,
            output l_leave).
         
         if l_leave
         then do:
            l_leave = false.
            leave.
         end. /* IF l_leave */
      end. /* FOR EACH pt_mstr */
   end. /* IF NOT l_ptp_avail */
end procedure. /* PROCEDURE p_find_ptp_det */


/* CHECK IF THE GIVEN COMBINATION OF PART, BOM CODE, PARENT AND  */
/* REFERENCE NUMBER IS ALREADY EXISTING. IF NOT EXISTING THEN WE */
/* CREATE THE TEMP TABLE OTHERWISE WE CLEAR THE ps_mstr BUFFER   */
procedure p_check_item_bom:

   define input  parameter ip-item          like ptp_part     no-undo.
   define input  parameter ip-bom           like ptp_bom_code no-undo.
   define input  parameter ip-par           like ps_par       no-undo.
   define input  parameter ip-ref           like ps_ref       no-undo.
   define input  parameter ip-check-level   like mfc_logical  no-undo.
   define output parameter op-leave         like mfc_logical  no-undo.

   for first item-bom
      where  item-bom.item = ip-item
      and    item-bom.bom  = ip-bom
      and    item-bom.par  = ip-par
      and    item-bom.ref  = ip-ref
   no-lock:
   end. /* FOR FIRST item-bom */

   if not available item-bom
   then do:

      run p_create_item_bom(input ip-item,
         input ip-bom,
         input ip-par,
         input ip-ref).

      op-leave = true.

   end.  /* IF NOT AVAILABLE item-bom */
   else do:
      if  (ip-check-level
         and level >= maxlevel
         and maxlevel <> 0)
         or not ip-check-level
      then do:

         for first ps_mstr
            where  recid(ps_mstr) =  -1
         no-lock:
         end.

         op-leave = true.

      end. /* IF  (ip-check-level... */

   end. /* ELSE DO */

end procedure. /* PROCEDURE p_check_item_bom */


/* CREATE TEMP-TABLE WITH A PART,BOM CODE,PARENT */
/* AND REFERENCE NUMBER COMBINATION              */
procedure p_create_item_bom:

   define input parameter ip-item like pt_part     no-undo.
   define input parameter ip-bom  like pt_bom_code no-undo.
   define input parameter ip-par  like ps_par      no-undo.
   define input parameter ip-ref  like ps_ref      no-undo.

   create item-bom.
   assign
      item-bom.item = ip-item
      item-bom.bom  = ip-bom
      item-bom.par  = ip-par
      item-bom.ref  = ip-ref.

end procedure. /* PROCEDURE p_create_item_bom */








